from database import engine
from database import Base

Base.metadata.create_all(bind=engine)


'''
这段代码的作用是假如没有建表，那么就建表，但我没试过，如果你运行这段代码有问题，可以参考下面建表代码即可


-- 使用数据库
CREATE DATABASE IF NOT EXISTS interview_agent DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE interview_agent;

-- 用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('candidate', 'admin') DEFAULT 'candidate',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username)
) COMMENT='系统用户';

-- 面试会话表
CREATE TABLE interview_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME,
    session_type VARCHAR(50) DEFAULT 'ai_interview',
    report_path VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) COMMENT='一次完整的面试会话';

-- 对话记录表
CREATE TABLE dialogues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    round INT NOT NULL,
    speaker ENUM('user', 'system') NOT NULL,
    content TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE,
    INDEX idx_session_round (session_id, round)
) COMMENT='一轮一轮的对话内容';

-- 情绪分析表
CREATE TABLE emotion_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    round INT NOT NULL,
    emotion_label VARCHAR(50),         -- e.g. happy, sad, neutral
    confidence FLOAT,
    image_path VARCHAR(255),           -- 表情截图路径
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE,
    INDEX idx_emotion_session (session_id, round)
) COMMENT='每轮对话的人脸表情识别结果';

-- 面试评估表
CREATE TABLE evaluations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    language_score FLOAT,              -- 内容表达
    emotion_score FLOAT,               -- 情绪状态
    logic_score FLOAT,                 -- 逻辑结构
    overall_score FLOAT,               -- 总分
    feedback TEXT,                     -- AI 或人工反馈
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES interview_sessions(id) ON DELETE CASCADE
) COMMENT='多维度面试结果评估';

-- 系统日志表（操作记录、错误等）
CREATE TABLE system_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    level ENUM('INFO', 'WARNING', 'ERROR') DEFAULT 'INFO',
    message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) COMMENT='系统运行日志';



'''
